package com.alks.common.utils.excelUtils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.afterturn.easypoi.util.PoiMergeCellUtil;
import com.alks.common.utils.DateUtils;
import com.alks.common.utils.ImageUtils;
import com.alks.common.utils.beanutils.ObjectUtils;
import com.alks.common.utils.excelUtils.data.ImageData;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

public class ExcelPoiUtils {

    public static Workbook createExcelWorkbook(TemplateExportParams params, Map<String, Object> map) {
        Workbook workbook = ExcelExportUtil.exportExcel(params, map);
        return workbook;
    }

    public static Drawing<?> createExcelPicture(Workbook workbook, Sheet sheet, byte[] array, Integer col1, Integer col2, Integer row1, Integer row2) {
        int picture = workbook.addPicture(array, workbook.PICTURE_TYPE_PNG);
        CreationHelper helper = workbook.getCreationHelper();
        Drawing<?> drawing = sheet.createDrawingPatriarch();
        ClientAnchor anchor = helper.createClientAnchor();
        anchor.setCol1(col1);
        anchor.setCol2(col2);
        anchor.setRow1(row1);
        anchor.setRow2(row2);
        // 设置图片锚点
        anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);
        Picture pict = drawing.createPicture(anchor, picture);
        //调整图片大小以适应单元格
        pict.resize();
        return drawing;
    }

    public static void writeExcelToResponse(Workbook workbook, HttpServletResponse response) {
        try {
            workbook.write(response.getOutputStream());
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 设置图片到表格
     *
     * @param response
     * @param params
     * @param map
     * @param array
     * @param col1     图片左上角所在单元格列号
     * @param col2     图片左上角所在单元格行号
     * @param row1     图片右下角所在单元格列号
     * @param row2     图片右下角所在单元格行号
     */
    public static void writeExcelWithPictureToResponse(HttpServletResponse response, TemplateExportParams params, Map<String, Object> map, byte[] array, Integer col1, Integer col2, Integer row1, Integer row2) {
        try {
            Workbook workbook = createExcelWorkbook(params, map);
            if (array != null && array.length > 0) {
                Sheet sheet = workbook.getSheetAt(0);
                createExcelPicture(workbook, sheet, array, col1, col2, row1, row2);
            }

            //根据sheets列表，设置数据行可见
            List<Integer> sheets = (List<Integer>) map.get("sheets");
            if (!ObjectUtils.isEmpty(sheets)){
                getDataShows(sheets, workbook);
            }
            writeExcelToResponse(workbook, response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 显示隐藏数据行
     * @param rows
     * @param workbook
     */
    public static void getDataShows(List<Integer> rows, Workbook workbook) {
        for (Integer r : rows) {
            Sheet sheet = workbook.getSheetAt(r);
            // 获取最后一行的索引
            int lastRowNum = sheet.getLastRowNum();
            for (int i = r; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    // 检查行是否隐藏
                    if (row.getZeroHeight() || row.getHeight()== 0) {
                        // 检查该行是否有数据
                        boolean hasData = false;
                        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                            Cell cell = row.getCell(j);
                            if (cell != null && cell.getCellType() != CellType.BLANK) {
                                hasData = true;
                                break;
                            }
                        }
                        // 如果该行有数据，则将其设置为可见
                        if (hasData) {
                            // 将行设置为可见
                            row.setZeroHeight(false);
                            // 设置行高为 400，适合显示内容
                            row.setHeight((short) 400);
                        }
                    }
                }
            }
        }
    }

    public static void writeExcelWithPictureToResponse(HttpServletResponse response, String filePath, Map<String, Object> map, byte[] array, Integer col1, Integer col2, Integer row1, Integer row2) {
        try {
            TemplateExportParams params = extracted(response, filePath);
            Workbook workbook = createExcelWorkbook(params, map);
            if (array != null) {
                Sheet sheet = workbook.getSheetAt(0);
                createExcelPicture(workbook, sheet, array, col1, col2, row1, row2);
            }
            writeExcelToResponse(workbook, response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void writeExcelWithPictureToResponse(HttpServletResponse response, String filePath, Map<String, Object> map, byte[] array, Integer col1, Integer col2, Integer row1, Integer row2, String outFile) {
        try {
            List<Object> list = (List<Object>) map.get("list");            outFile = URLEncoder.encode(outFile,"UTF-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + outFile);
            TemplateExportParams params = extracted(response, filePath);
            Workbook workbook = createExcelWorkbook(params, map);
            Sheet sheet = workbook.getSheetAt(0);
            if (array != null) {
                createExcelPicture(workbook, sheet, array, col1, col2, row1, row2);
            }
            writeExcelToResponse(workbook, response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static void writeExcelWithPictureToResponse(HttpServletResponse response, String filePath, Map<String, Object> map, byte[] array, Integer col1, Integer col2, Integer row1, Integer row2, String outFile,Integer colStart1,Integer colEnd1,Integer rowStart1,Integer colStart2,Integer colEnd2,Integer rowStart2,Integer pageSize) {
        try {
            List<Object> list = (List<Object>) map.get("list");
            outFile = URLEncoder.encode(outFile,"UTF-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + outFile);
            TemplateExportParams params = extracted(response, filePath);
            Workbook workbook = createExcelWorkbook(params, map);
            Sheet sheet = workbook.getSheetAt(0);
            for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum()-1; rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    continue; // 跳过空行
                }
                if (rowIndex>=43) break;
                boolean hasData = false;
                // 检查行中是否有数据
                for (Cell cell : row) {
                    if (cell != null && !isCellEmpty(cell)) {
                        hasData = true;
                        break;
                    }
                }
                // 如果行中没有数据，则设置为隐藏
                if (!hasData) {
                    row.setHeight((short) 0);
                    // 设置行高为0以隐藏行
                }
            }

            if (array != null) {
                createExcelPicture(workbook, sheet, array, col1, col2, row1, row2);
            }
            writeExcelToResponse(workbook, response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    private static boolean isCellEmpty(Cell cell) {
        switch (cell.getCellType()) {
            case BLANK:
                return true;
            case STRING:
                return cell.getStringCellValue().trim().isEmpty();
            case NUMERIC:
                return cell.getNumericCellValue() == 0;
            case BOOLEAN:
                return false;
            case ERROR:
                return true;
            case FORMULA:
                return cell.getCellFormula().trim().isEmpty();
            default:
                return true;
        }
    }
    public static void writeExcelWithPictureToResponse(HttpServletResponse response, String filePath, Map<String, Object> map, byte[] array, Integer col1, Integer col2, Integer row1, Integer row2, String outFile,Integer colStart1,Integer colEnd1,Integer rowStart1 ) {
        try {
            List<Object> list = (List<Object>) map.get("list");
            outFile = URLEncoder.encode(outFile,"UTF-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + outFile);
            TemplateExportParams params = extracted(response, filePath);
            Workbook workbook = createExcelWorkbook(params, map);
            Sheet sheet = workbook.getSheetAt(0);
            for (Object o : list) {
                CellRangeAddress mergedRegion;
                rowStart1++;
                for (int i = sheet.getNumMergedRegions() - 1; i >= 0; i--) {
                    mergedRegion = sheet.getMergedRegion(i);
                    if (!(rowStart1 > mergedRegion.getLastRow() || rowStart1 < mergedRegion.getFirstRow() || colStart1 > mergedRegion.getLastColumn() || colEnd1 < mergedRegion.getFirstColumn())) {
                        sheet.removeMergedRegion(i);
                    }
                }
                mergedRegion = new CellRangeAddress(rowStart1, rowStart1,colStart1,colEnd1);
                sheet.addMergedRegion(mergedRegion);
            }
            if (array != null) {
                createExcelPicture(workbook, sheet, array, col1, col2, row1, row2);
            }
            writeExcelToResponse(workbook, response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void writeExcelList(HttpServletResponse response, Map<String, Object> map, String filePath) throws UnsupportedEncodingException {
        ExcelUtils.setResponseZF(response, DateUtils.dateTimeNow()+".xlsx");
        TemplateExportParams params = extracted(response, filePath);
        Workbook workbook = createExcelWorkbook(params, map);
        writeExcelToResponse(workbook, response);
    }

    private static TemplateExportParams extracted(HttpServletResponse response, String filePath) {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        TemplateExportParams params = new TemplateExportParams(
                filePath);
        return params;
    }

    /**
     * @param response 响应
     * @param map      数据
     * @param templatePath 模板文件路径
     * @param fileName 下载的文件名
     * @throws UnsupportedEncodingException
     */
    public static void writeExcelList(HttpServletResponse response, Map<String, Object> map, String templatePath, String fileName) throws UnsupportedEncodingException {
        ExcelUtils.setResponseZF(response, fileName);
        TemplateExportParams params = extracted(response, templatePath);
        Workbook workbook = createExcelWorkbook(params, map);
        writeExcelToResponse(workbook, response);
    }

    /*导出模板*/
    public static void writeExcelList1(HttpServletResponse response, Map<String, Object> map,  TemplateExportParams params) throws UnsupportedEncodingException {
        Workbook workbook = createExcelWorkbook(params, map);
        writeExcelToResponse(workbook, response);
    }

    /**
     * 分批导出模板：
     * 如果list数据超过65535
     * 分批导出
     */
    public static void writeExceBatchlListZIP(HttpServletResponse response,String fileName, Map<String, Object> map, TemplateExportParams params, int batchSize) throws IOException {

        // 创建Zip输出流
        ZipOutputStream zipOut = new ZipOutputStream(response.getOutputStream());

        // 获取数据列表
        List<?> dataList = (List<?>) map.get("list");

        // 计算总批次数量
        int totalSize = dataList.size();
        int totalBatches = (int) Math.ceil((double) totalSize / batchSize);

        // 处理每一批数据
        for (int i = 0; i < totalBatches; i++) {
            int start = i * batchSize;
            int end = Math.min(start + batchSize, totalSize);

            // 获取子列表
            List<?> subList = dataList.subList(start, end);

            // 更新数据映射中的列表
            Map<String, Object> batchDataMap = new HashMap<>(map);
            batchDataMap.put("list", subList);

            // 判断是否是最后一批数据，并调整 countAmt
            adjustCountAmtForBatch(batchDataMap, i == totalBatches - 1);

            // 创建 Excel 工作簿
            Workbook workbook = ExcelExportUtil.exportExcel(params, batchDataMap);

            // 创建内存输出流
            try (ByteArrayOutputStream out = new ByteArrayOutputStream()) {
                // 将Excel文件写入内存输出流
                workbook.write(out);

                // 创建ZIP条目并写入Zip输出流
                ZipEntry entry = new ZipEntry(+ (i + 1) + fileName);
                zipOut.putNextEntry(entry);
                zipOut.write(out.toByteArray());
                zipOut.closeEntry();
            } finally {
                workbook.close();
            }
        }
    }

    // 处理总计数据所在页面
    private static void adjustCountAmtForBatch(Map<String, Object> dataMap, boolean isLastBatch) {
        if (!isLastBatch) {
            dataMap.put("countAmt", null);
        }
    }


    //单独一行的情况
    public static void writeExcelList2(HttpServletResponse response, Map<String, Object> map, List<String> listOther, String filePath, String fileName,Integer startRow,Integer startCell) throws UnsupportedEncodingException {
        ExcelUtils.setResponseZF(response, fileName);
        TemplateExportParams params = extracted(response, filePath);
        Workbook workbook = createExcelWorkbook(params, map);

        if(listOther!=null && listOther.size()>0){
            //获取第一个sheet
            Sheet sheetAt = workbook.getSheetAt(0);
            //3.创建行对象，从3开始 （这个就是指定在哪一行） i通过循环会往上加嘛
            Row row = sheetAt.createRow(startRow);
            for (int i = 0; i < listOther.size(); i++) {
                //4.创建单元格，从6开始 （这个就是指定在哪一列）
                Cell cell = row.createCell(startCell+i);
                //5.单元格写入数据 （上面的Row和Cell 就相当于文档里的坐标，到这一步写入到文档里面）
                String s = listOther.get(i);
                cell.setCellValue(s);
            }
        }
        writeExcelToResponse(workbook, response);
    }

    /**
     * 自定义模板（边框样式跟着数据变化）
     * @param response
     * @param map 模板里面其他可以填充的数据
     * @param listOther 复杂页面的list List<List<String>>
     * @param filePath 模板路径
     * @param fileName 模板名称
     * @param startRow 处理数据开始的行数
     * @param startCell 处理数据开始的列数
     * @param headerStyle 数据头单元格格式*
     * @param dataStyle 数据单元格格式*
     * @param mergeRule 是否开启合并规则
     * @param mergeBuildList 合并规则实例
     * @throws UnsupportedEncodingException
     */
    public static void writeExcelList2(HttpServletResponse response, Map<String, Object> map, List<List<String>> listOther,
                                       String filePath, String fileName,
                                       Integer startRow,Integer startCell,
                                       CellStyleBuild headerStyle, CellStyleBuild dataStyle,
                                       Boolean mergeRule,List<MergeBuild> mergeBuildList) throws UnsupportedEncodingException {
        ExcelUtils.setResponseZF(response, fileName);
        TemplateExportParams params = extracted(response, filePath);
        Workbook workbook = createExcelWorkbook(params, map);
        if(listOther!=null && listOther.size()>0){
            //1.获取第一个sheet
            Sheet sheetAt = workbook.getSheetAt(0);

            // 2.设置单元格样式
            CellStyle headerCellStyle = CellStyleBuild.createCellStyle(workbook, headerStyle);
            CellStyle dataCellStyle = CellStyleBuild.createCellStyle(workbook, dataStyle);

            /*一个一个单元格设置值*/
            for (int i = 0; i < listOther.size(); i++) {//行
                List<String> strings = listOther.get(i);
                //3.创建行对象，从startRow开始 （这个就是指定在哪一行） i通过循环会往上加嘛
                Row row = sheetAt.createRow(startRow+i);
                // 设置行高，例如设置为20（默认单位为点，1点=1/72英寸）
                row.setHeightInPoints(20);
                if(strings!=null && strings.size()>0){
                    for (int j = 0; j < strings.size(); j++) {//列
                        //4.创建单元格，从startCell开始 （这个就是指定在哪一列）
                        Cell cell = row.createCell(startCell+j);
                        String s = strings.get(j);
                        //5.单元格写入数据 （上面的Row和Cell 就相当于文档里的坐标，到这一步写入到文档里面）
                        cell.setCellValue(s);
                        // 6.应用样式，第一行使用headerCellStyle，其余行使用dataCellStyle
                        cell.setCellStyle(i == 0? headerCellStyle : dataCellStyle);
                    }
                }
            }

            /*是否合并单元格*/
            // 合并指定列，从第三行开始，到数据的最后一行结束
            if(mergeRule){
//                CellRangeAddress cellAddresses = MergeBuild.CustomMergeRule(mergeBuild.getStartRow(), mergeBuild.getEndRow(), mergeBuild.getStartCol(), mergeBuild.getEndCol());
//                sheetAt.addMergedRegion(cellAddresses);
                for (MergeBuild MergeBuild : mergeBuildList) {
                    // 对于每个合并规则，执行合并操作
                    if(MergeBuild != null) {
                        CellRangeAddress cellAddresses = MergeBuild.CustomMergeRule(
                                MergeBuild.getStartRow(),
                                MergeBuild.getEndRow(),
                                MergeBuild.getStartCol(),
                                MergeBuild.getEndCol()
                        );
                        sheetAt.addMergedRegion(cellAddresses);
                    }
                }
            }

        }
        writeExcelToResponse(workbook, response);
    }

    /**
     * 自定义模板2（边框样式跟着固定）
     * @param response
     * @param map 模板里面其他可以填充的数据
     * @param listOther 复杂页面的list List<List<String>>
     * @param filePath 模板路径
     * @param fileName 模板名称
     * @param startRow 处理数据开始的行数
     * @param startCell 处理数据开始的列数
     * @param borderCell 自定义边框线截至的列数:第几列开始没有边款
     * @param headerRowIndexList 控制哪几列的样式是headerStyle
     * @param headerStyle 数据头单元格格式*
     * @param dataStyle 数据单元格格式*
     * @param mergeRule 是否开启合并规则
     * @param mergeBuildList 合并规则实例
     * @throws UnsupportedEncodingException
     */
    public static void writeExcelList2(HttpServletResponse response, Map<String, Object> map, List<List<String>> listOther,
                                       String filePath, String fileName,
                                       Integer startRow,Integer startCell,Integer borderCell,List<Integer> headerRowIndexList,
                                       CellStyleBuild headerStyle, CellStyleBuild dataStyle,
                                       Boolean mergeRule,List<MergeBuild> mergeBuildList) throws UnsupportedEncodingException {
        ExcelUtils.setResponseZF(response, fileName);
        TemplateExportParams params = extracted(response, filePath);
        Workbook workbook = createExcelWorkbook(params, map);

        if(listOther!=null && listOther.size()>0){
            //1.获取第一个sheet
            Sheet sheetAt = workbook.getSheetAt(0);

            // 2.设置单元格样式
            CellStyle headerCellStyle = CellStyleBuild.createCellStyle(workbook, headerStyle);
            CellStyle dataCellStyle = CellStyleBuild.createCellStyle(workbook, dataStyle);
            CellStyle noBorderCellStyle = CellStyleBuild.createBorderCellStyle(workbook,BorderStyle.NONE); // 边框样式
            CellStyle borderCellStyle = CellStyleBuild.createBorderCellStyle(workbook,BorderStyle.THIN); // 边框样式

            headerCellStyle.setWrapText(true); // 设置自动换行
            dataCellStyle.setWrapText(true); // 设置自动换行
            noBorderCellStyle.setWrapText(true); // 设置自动换行
            borderCellStyle.setWrapText(true); // 设置自动换行

            /*一个一个单元格设置值*/
            for (int i = 0; i < listOther.size(); i++) {//行
                List<String> strings = listOther.get(i);
                //3.创建行对象，从startRow开始 （这个就是指定在哪一行） i通过循环会往上加嘛
                Row row = sheetAt.createRow(startRow+i);
                // 设置行高，例如设置为20（默认单位为点，1点=1/72英寸）
                row.setHeightInPoints(20);
                if(strings!=null && strings.size()>0){
                    //列
                    // 确保至少创建到borderCell列
                    for (int j = 0; j < borderCell; j++) { //列
                        Cell cell = row.createCell(startCell + j);

                        // 如果有对应的数据，则设置数据；否则，设置为空字符串以确保单元格存在
                        String content = (j < strings.size()) ? strings.get(j) : "";
                        //cell.setCellValue(content);

                        // 判断内容是否为数字
                        if (content!= null && content != "" && isNumeric(content)) {
                            double number = Double.parseDouble(content);
                            cell.setCellValue(number);
                        } else {
                            cell.setCellValue(content);
                        }

                        // 应用样式，根据是否在headerRowIndexList中选择样式
                        cell.setCellStyle(headerRowIndexList.contains(i) ? headerCellStyle : dataCellStyle);

                        // 超过限制列，设置超出数据无边框；未到限制列，设置超出数据有边框
                        if (j >= borderCell - 1) {
                            cell.setCellStyle(noBorderCellStyle);
                        } else if(j >= strings.size()){
                            cell.setCellStyle(borderCellStyle);
                        }
                    }
                }
            }

            /*是否合并单元格*/
            // 合并指定列，从第三行开始，到数据的最后一行结束
            if(mergeRule){
                for (MergeBuild MergeBuild : mergeBuildList) {
                    // 对于每个合并规则，执行合并操作
                    if(MergeBuild != null) {
                        CellRangeAddress cellAddresses = MergeBuild.CustomMergeRule(
                                MergeBuild.getStartRow(),
                                MergeBuild.getEndRow(),
                                MergeBuild.getStartCol(),
                                MergeBuild.getEndCol()
                        );
                        sheetAt.addMergedRegion(cellAddresses);
                    }
                }
            }

        }
        writeExcelToResponse(workbook, response);
    }

    public static void writeExcelList222(HttpServletResponse response, Map<String, Object> map, List<List<String>> listOther,
                                         TemplateExportParams params,
                                       Integer startRow,Integer startCell,Integer borderCell,List<Integer> headerRowIndexList,
                                       CellStyleBuild headerStyle, CellStyleBuild dataStyle,
                                       Boolean mergeRule,List<MergeBuild> mergeBuildList) throws UnsupportedEncodingException {
        Workbook workbook = createExcelWorkbook(params, map);

        if(listOther!=null && listOther.size()>0){
            //1.获取第一个sheet
            Sheet sheetAt = workbook.getSheetAt(0);

            // 2.设置单元格样式
            CellStyle headerCellStyle = CellStyleBuild.createCellStyle(workbook, headerStyle);
            CellStyle dataCellStyle = CellStyleBuild.createCellStyle(workbook, dataStyle);
            CellStyle noBorderCellStyle = CellStyleBuild.createBorderCellStyle(workbook,BorderStyle.NONE); // 边框样式
            CellStyle borderCellStyle = CellStyleBuild.createBorderCellStyle(workbook,BorderStyle.THIN); // 边框样式

            headerCellStyle.setWrapText(true); // 设置自动换行
            dataCellStyle.setWrapText(true); // 设置自动换行
            noBorderCellStyle.setWrapText(true); // 设置自动换行
            borderCellStyle.setWrapText(true); // 设置自动换行

            /*一个一个单元格设置值*/
            for (int i = 0; i < listOther.size(); i++) {//行
                List<String> strings = listOther.get(i);
                //3.创建行对象，从startRow开始 （这个就是指定在哪一行） i通过循环会往上加嘛
                Row row = sheetAt.createRow(startRow+i);
                // 设置行高，例如设置为20（默认单位为点，1点=1/72英寸）
                row.setHeightInPoints(20);
                if(strings!=null && strings.size()>0){
                    //列
                    // 确保至少创建到borderCell列
                    for (int j = 0; j < Math.max(borderCell, strings.size()); j++) { //列
                        Cell cell = row.createCell(startCell + j);

                        // 如果有对应的数据，则设置数据；否则，设置为空字符串以确保单元格存在
                        String content = (j < strings.size()) ? strings.get(j) : "";
                        //cell.setCellValue(content);

                        //判断内容是否为数字
                        if (content!= null && content != "" && isNumeric(content)) {
                            double number = Double.parseDouble(content);
                            cell.setCellValue(number);
                        } else {
                            cell.setCellValue(content);
                        }

                        // 应用样式，根据是否在headerRowIndexList中选择样式
                        cell.setCellStyle(headerRowIndexList.contains(i) ? headerCellStyle : dataCellStyle);

                        // 超过限制列，设置超出数据无边框；未到限制列，设置超出数据有边框
                        if (j >= borderCell - 1) {
                            cell.setCellStyle(noBorderCellStyle);
                        } else if(j >= strings.size()){
                            cell.setCellStyle(borderCellStyle);
                        }
                    }
                }
            }

            /*是否合并单元格*/
            // 合并指定列，从第三行开始，到数据的最后一行结束
            if(mergeRule){
                for (MergeBuild MergeBuild : mergeBuildList) {
                    // 对于每个合并规则，执行合并操作
                    if(MergeBuild != null) {
                        CellRangeAddress cellAddresses = MergeBuild.CustomMergeRule(
                                MergeBuild.getStartRow(),
                                MergeBuild.getEndRow(),
                                MergeBuild.getStartCol(),
                                MergeBuild.getEndCol()
                        );
                        sheetAt.addMergedRegion(cellAddresses);
                    }
                }
            }

        }
        writeExcelToResponse(workbook, response);
    }

    //writeExcelList222中修改成可以把数字在导出时显示数字
    public static void writeExcelListByConvertiToNumber(HttpServletResponse response, Map<String, Object> map, List<List<String>> listOther,
                                         TemplateExportParams params,
                                         Integer startRow,Integer startCell,Integer borderCell,List<Integer> headerRowIndexList,
                                         CellStyleBuild headerStyle, CellStyleBuild dataStyle,
                                         Boolean mergeRule,List<MergeBuild> mergeBuildList) throws UnsupportedEncodingException {
        Workbook workbook = createExcelWorkbook(params, map);

        if(listOther!=null && listOther.size()>0){
            //1.获取第一个sheet
            Sheet sheetAt = workbook.getSheetAt(0);

            // 2.设置单元格样式
            CellStyle headerCellStyle = CellStyleBuild.createCellStyle(workbook, headerStyle);
            CellStyle dataCellStyle = CellStyleBuild.createCellStyle(workbook, dataStyle);
            CellStyle noBorderCellStyle = CellStyleBuild.createBorderCellStyle(workbook,BorderStyle.NONE); // 边框样式
            CellStyle borderCellStyle = CellStyleBuild.createBorderCellStyle(workbook,BorderStyle.THIN); // 边框样式

            headerCellStyle.setWrapText(true); // 设置自动换行
            dataCellStyle.setWrapText(true); // 设置自动换行
            noBorderCellStyle.setWrapText(true); // 设置自动换行
            borderCellStyle.setWrapText(true); // 设置自动换行

            /*一个一个单元格设置值*/
            for (int i = 0; i < listOther.size(); i++) {//行
                List<String> strings = listOther.get(i);
                //3.创建行对象，从startRow开始 （这个就是指定在哪一行） i通过循环会往上加嘛
                Row row = sheetAt.createRow(startRow+i);
                // 设置行高，例如设置为20（默认单位为点，1点=1/72英寸）
                row.setHeightInPoints(20);
                if(strings!=null && strings.size()>0){
                    //列
                    // 确保至少创建到borderCell列
                    for (int j = 0; j < Math.max(borderCell, strings.size()); j++) { //列
                        Cell cell = row.createCell(startCell + j);

                        // 如果有对应的数据，则设置数据；否则，设置为空以确保单元格存在
                        String content = (j < strings.size()) ? strings.get(j) : "";
                        //cell.setCellValue(content);

                        //判断内容是否为数字
                        if (content!= null && content != "" && isNumeric(content)) {
                            double number = Double.parseDouble(content);
                            cell.setCellValue(number);
                        } else {
                            cell.setCellValue(content);
                        }

                        // 应用样式，根据是否在headerRowIndexList中选择样式
                        cell.setCellStyle(headerRowIndexList.contains(i) ? headerCellStyle : dataCellStyle);

                        // 超过限制列，设置超出数据无边框；未到限制列，设置超出数据有边框
                        if (j >= borderCell - 1) {
                            cell.setCellStyle(noBorderCellStyle);
                        } else if(j >= strings.size()){
                            cell.setCellStyle(borderCellStyle);
                        }
                    }
                }
            }

            /*是否合并单元格*/
            // 合并指定列，从第三行开始，到数据的最后一行结束
            if(mergeRule){
                for (MergeBuild MergeBuild : mergeBuildList) {
                    // 对于每个合并规则，执行合并操作
                    if(MergeBuild != null) {
                        CellRangeAddress cellAddresses = MergeBuild.CustomMergeRule(
                                MergeBuild.getStartRow(),
                                MergeBuild.getEndRow(),
                                MergeBuild.getStartCol(),
                                MergeBuild.getEndCol()
                        );
                        sheetAt.addMergedRegion(cellAddresses);
                    }
                }
            }

        }
        writeExcelToResponse(workbook, response);
    }


    /**
     * 获取excel版本
     * @param excelFileName
     * @return
     */
    public static int getExcelVersion(String excelFileName) {
        int flag = 0;
        if (excelFileName.matches("^.+\\.(?i)(xls)$")) {
            flag = 1;
        }
        if (excelFileName.matches("^.+\\.(?i)(xlsx)$")) {
            flag = 2;
        }
        return flag;
    }

    /**
     * 把字符串转换成数字：可以转的就转，不能转的就不转
     * @param str 输入的字符串
     * @return 转换成功返回数字，失败返回原始字符串
     */
    public static Object isConvertibleToNumber(String str) {
        try {
            if (str.contains(".")) {
                return Double.parseDouble(str);
            } else {
                return Integer.parseInt(str);
            }
        } catch (NumberFormatException e) {
            return str;
        }
    }

    /**
     * 把数字转成数字
     * @param str
     * @return
     */
    private static boolean isNumeric(String str) {
        try {
            Double.parseDouble(str);
            return true;
        } catch (NumberFormatException e) {
            return false;
        }
    }

    /**
     * 通过模板导出Excel，附带多图
     * @param response
     * @param params
     * @param map
     * @param images
     */
    public static void writeExcelByTemplateWithImages(HttpServletResponse response, TemplateExportParams params, Map<String, Object> map, List<ImageData> images) {
        try {
            Workbook workbook = createExcelWorkbook(params, map);
            Sheet sheet = workbook.getSheetAt(0);
            if (!CollectionUtils.isEmpty(images)) {
                for (ImageData image : images) {
                    byte[] imageBytes = ImageUtils.readAndResizeImage(image.getImgUrl(), image.getWidth(), image.getHigh());
                    if (imageBytes == null || imageBytes.length == 0) {
                        continue;
                    }
                    int picture = workbook.addPicture(imageBytes, workbook.PICTURE_TYPE_PNG);
                    CreationHelper helper = workbook.getCreationHelper();
                    Drawing<?> drawing = sheet.createDrawingPatriarch();
                    ClientAnchor anchor = helper.createClientAnchor();
                    anchor.setCol1(image.getCol());
                    anchor.setCol2(image.getCol() + 1);
                    anchor.setRow1(image.getRow());
                    anchor.setRow2(image.getRow() + 1);
                    // 设置图片锚点
                    anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
                    Picture pict = drawing.createPicture(anchor, picture);
                    //调整图片大小以适应单元格
                    pict.resize();

                }
            }

            //根据sheets列表，设置数据行可见
            List<Integer> sheets = Arrays.asList(0);
            if (!ObjectUtils.isEmpty(sheets)){
                deleteEmptyLine(sheets, workbook);
            }

            writeExcelToResponse(workbook, response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 显示隐藏数据行并删除没有数据的行
     * @param sheetIndexes
     * @param workbook
     */
    public static void deleteEmptyLine(List<Integer> sheetIndexes, Workbook workbook) {
        for (Integer sheetIndex : sheetIndexes) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            int lastRowNum = sheet.getLastRowNum();

            // 获取所有的合并单元格区域
            List<CellRangeAddress> mergedRegions = new ArrayList<>();
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                mergedRegions.add(sheet.getMergedRegion(i));
            }

            // 删除行之前，先移除所有的合并单元格
            for (int i = 0; i < mergedRegions.size(); i++) {
                sheet.removeMergedRegion(0);
            }

            // 从最后一行开始遍历，避免删除行时索引变化的问题
            for (int i = lastRowNum; i >= 0; i--) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    // 检查该行是否隐藏
                    if (row.getZeroHeight() || row.getHeight() == 0) {
                        // 检查该行是否有数据
                        boolean hasData = false;
                        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                            Cell cell = row.getCell(j);
                            if (cell != null && cell.getCellType() != CellType.BLANK) {
                                hasData = true;
                                break;
                            }
                        }
                        // 如果该行没有数据，则删除该行
                        if (!hasData) {
                            justMergedRegion(mergedRegions, row);
                            sheet.removeRow(row);
                            sheet.shiftRows(i + 1, lastRowNum, -1); // 删除行后，所有后续的行需要向上移动
                        } else {
                            // 需要恢复该行的显示
                            row.setZeroHeight(false); // 恢复行的可见性
//                            row.setHeight((short) 400); // 设置行高以适应显示内容
                        }
                    }
                }
            }

            // 重新添加合并区域
            for (CellRangeAddress mergedRegion : mergedRegions) {
                sheet.addMergedRegion(mergedRegion);
            }
        }
    }

    /**
     * 调整合并区域
     * @param mergedRegions
     * @param row
     */
    private static void justMergedRegion(List<CellRangeAddress> mergedRegions, Row row) {
        int rowNum = row.getRowNum();
        // 遍历所有合并区域，如果该行属于合并区域，删除该合并区域
        for (int i = mergedRegions.size() - 1; i >= 0; i--) {
            CellRangeAddress mergedRegion = mergedRegions.get(i);
            if (mergedRegion.getLastRow() > mergedRegion.getFirstRow()) {
                if (mergedRegion.getFirstRow() < rowNum && mergedRegion.getLastRow() >= rowNum) {
                    mergedRegion.setLastRow(mergedRegion.getLastRow() - 1);
                } else if (mergedRegion.getFirstRow() >= rowNum) {
                    mergedRegion.setFirstRow(mergedRegion.getFirstRow() - 1);
                    mergedRegion.setLastRow(mergedRegion.getLastRow() - 1);
                }
            } else {
                if (mergedRegion.getFirstRow() == rowNum) {
                    mergedRegions.remove(i);
                } else if (mergedRegion.getFirstRow() > rowNum) {
                    mergedRegion.setFirstRow(mergedRegion.getFirstRow() - 1);
                    mergedRegion.setLastRow(mergedRegion.getLastRow() - 1);
                }
            }
        }
    }
}
